
DROP TABLE IF EXISTS account;

-- 创建账户表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name varchar(10),
	money double(10,2)
);

-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);

UPDATE account set money = 1000;

select * from account;

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

-- 出现异常了...
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交  0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;

-- 班级平均分
select  students.name as name ,avg(scores.scores) as avg_score from students inner join  scores on students.student_id = scores.student_id group by students.name ORDER BY avg_score DESC;

-- 没有参加考试的学生
select students.name as name from students left join scores on students.student_id = scores.score_id where scores.score_id is null;

select students.name as name ,scores.scores as score from students left join scores as s on students.student_id = scores.score_id where s.subject = '数学' and scores.score > 90;

-- 查询每个学生的总分，降序
select s.student_id,s.name,sum(sc.score) from students as s inner join scores as sc on s.student_id = sc.student_id group by s.student_id,s.name order by sum(sc.score) desc;
-- 查询没有数学成绩的学生名单(错误写法)
select s.student_id,s.name from students as s left join scores as sc on s.student_id = sc.student_id where sc.subject is not  '数学'
-- 查询没有数学成绩的学生名单（正确写法）
SELECT s.student_id, s.name
FROM students s
         LEFT JOIN (
    SELECT DISTINCT student_id
    FROM scores
    WHERE subject = '数学'
) sc ON s.student_id = sc.student_id
WHERE sc.student_id IS NULL;
-- 查询每个班级的人数（不会）
SELECT class_id, COUNT(*) AS student_count
FROM students
GROUP BY class_id;
-- 查询每个学生的语文成绩
select s.student_id,s.name from students as s left join scores as sc on s.student_id = sc.student_id where sc.subject =  '语文';
-- 查询平均分大于80分的学生姓名和平均分（写错了）
select s.student_id,s.name,avg(sc.score)from students as s left join scores as sc on s.student_id = sc.student_id where (select * from sc where avg(sc.score)>80);
-- 查询平均分大于80分的学生姓名和平均分(正确版本)
SELECT s.name, AVG(sc.score) AS avg_score
FROM students s
         JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.name
HAVING avg_score > 80;
-- 找出年龄最小的人（错误版本）
select s.student_id,s.name，min(s.age) from students as s gorup by s.student_id,s.name;
-- 找出年龄最小的人（正确版本）
SELECT student_id, name, age
FROM students
ORDER BY age ASC
LIMIT 1;  -- 只返回年龄最小的学生
-- 查出数学科目中分数最高的学生姓名和分数
SELECT s.name, sc.score
FROM students s
         JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.subject = '数学'
ORDER BY sc.score DESC
LIMIT 1;
-- 任何没有参加考试的学生
SELECT *
FROM students
WHERE student_id NOT IN (
    SELECT DISTINCT student_id FROM scores
);